package ookweb;

import java.io.BufferedWriter;
import java.io.PrintStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import oracle.sql.CLOB;

public class Conn {
	private static Connection con;
	private Statement stmt;
	private ResultSet rs;
	private static final String drivername = "oracle.jdbc.driver.OracleDriver";
	private static String url = "jdbc:oracle:thin:@zhangar.vicp.net:1521:orcl";
	private static String dbuser = "cxzlr";
	private static String dbpass = "cxzlr197980";

	public Conn() {
	}

	public Conn(String u, String p, String link) {
		dbuser = u;
		dbpass = p;
		url = link;
	}

	public static synchronized Connection getCon() throws Exception {
		try {
			Class.forName(drivername);
			con = DriverManager.getConnection(url, dbuser, dbpass);
			return con;
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw e;
		}
	}

	public Statement getStmtread() {
		try {
			con = getCon();
			this.stmt = con.createStatement(1004, 1007);
			return this.stmt;
		} catch (Exception e) {
			System.err.println(e.getMessage());
			e.printStackTrace();
		}
		return null;
	}

	public int insert2(String sql1, String sql2, String content) {
		try {
			Class.forName(drivername);
			Connection conn = DriverManager.getConnection(url, dbuser, dbpass);
			conn.setAutoCommit(false);

			PreparedStatement ps1 = conn.prepareStatement(sql1);
			ps1.executeUpdate();
			ps1.close();

			PreparedStatement ps2 = conn.prepareStatement(sql2);
			ResultSet rs2 = ps2.executeQuery();
			while (rs2.next()) {
				CLOB clob = (CLOB) rs2.getClob("content");
				BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());

				out.write(content, 0, content.length());
				out.close();
			}
			conn.commit();
			conn.setAutoCommit(true);
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return 1;
	}

	public int update2(String sql1, String sql2, String content) {
		try {
			Class.forName(drivername);
			Connection conn = DriverManager.getConnection(url, dbuser, dbpass);
			conn.setAutoCommit(false);
			PreparedStatement ps1 = conn.prepareStatement(sql1);
			ps1.executeUpdate();
			ps1.close();
			PreparedStatement ps2 = conn.prepareStatement(sql2);
			ResultSet rs2 = ps2.executeQuery();
			while (rs2.next()) {
				CLOB clob = (CLOB) rs2.getClob("content");
				BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());
				out.write(content, 0, content.length());
				out.close();
			}
			conn.commit();
			conn.setAutoCommit(true);
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return 1;
	}

	public ResultSet getRs(String sql) {
		try {
			this.stmt = getStmtread();
			this.rs = this.stmt.executeQuery(sql);
			return this.rs;
		} catch (Exception e) {
			System.err.println(e.getMessage());
			e.printStackTrace();
		}
		return null;
	}

	public Statement getStmt() {
		try {
			con = getCon();
			this.stmt = con.createStatement();
			return this.stmt;
		} catch (Exception e) {
			System.err.println(e.getMessage());
			e.printStackTrace();
		}
		return null;
	}

	public String getPage(int p, int allocunt, int allp, String url) {
		String pagehtml = "";
		if (allocunt != 0) {
			int ap = p + 1;
			int np = p - 1;
			String page_temp = "";
			int startp = p - 2;

			if (startp <= 0) {
				startp = 1;
			}

			for (int isp = startp; isp <= startp + 4; isp++) {
				if (isp == startp + 4) {
					if (isp == p)
						page_temp = page_temp + "<a class=\"on\">" + isp + "</a>";
					else {
						page_temp = page_temp + "<a href=\"" + url + "&pages=" + isp + "\">" + isp + "</a>";
					}
				} else if (isp == p)
					page_temp = page_temp + "<a class=\"on\">" + isp + "</a>";
				else {
					page_temp = page_temp + "<a href=\"" + url + "&pages=" + isp + "\">" + isp + "</a>";
				}

				if (isp != allp)
					continue;
				isp = startp + 5;
			}

			if (p == 1) {
				if (p == allp)
					pagehtml = pagehtml + "<a>首页</a><a>[上一页]</a>" + page_temp + "<a>[下一页]</a><a>尾页</a>";
				else {
					pagehtml = pagehtml + "<a>首页</a><a>[上一页]</a>" + page_temp + "<a href=\"" + url + "&pages=" + ap + "\">[下一页]</a><a href=\"" + url + "&pages=" + allp
							+ "\">尾页</a>";
				}
			} else if (p == allp)
				pagehtml = pagehtml + "<a href=\"" + url + "&pages=1\">首页</a><a href=\"" + url + "&pages=" + np + "\">[上一页]</a>" + page_temp + "<a >[下一页]</a><a >尾页</a>";
			else {
				pagehtml = pagehtml + "<a href=\"" + url + "&pages=1\">首页<a><a href=\"" + url + "&pages=" + np + "\">[上一页]</a>" + page_temp + "<a href=\"" + url + "&pages=" + ap
						+ "\">[下一页]</a><a href=\"" + url + "&pages=" + allp + "\">尾页</a>";
			}

		}

		return pagehtml;
	}

	public synchronized void close() {
		try {
			if (this.rs != null) {
				this.rs.close();
				this.rs = null;
			}
		} catch (Exception e) {
			System.err.println(e.getMessage());
			e.printStackTrace();
		}
		try {
			if (this.stmt != null) {
				this.stmt.close();
				this.stmt = null;
			}
		} catch (Exception e) {
			System.err.println(e.getMessage());
			e.printStackTrace();
		}
		try {
			if (con != null) {
				con.close();
				con = null;
			}
		} catch (Exception e) {
			System.err.println(e.getMessage());
			e.printStackTrace();
		}
	}
}
